USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[KozinsyozokuSyukei] スクリプト日付: 2014/05/14 17:30:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KozinsyozokuSyukei]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[KozinsyozokuSyukei];
GO

CREATE PROCEDURE [dbo].[KozinsyozokuSyukei]
	@TAIKAINO int,
	@TAIKOSENNO int
AS
	SELECT 
		A.TAIKOSENNO,
		A.HOUHOU,
		KOJINSYOZOKU.KOJINSYOZOKUTIMEI,
		B.SYUKEINO,
		B.JYUNI,
		B.TOKUTEN, 
		B.BONUS,
		B.SANKA,
		D.PRINTNO 
	FROM 
		TBL_TAIKOU AS A 
	LEFT JOIN 
		TBL_TAIKOU_KEKKA AS B 
	ON 
		A.TAIKAINO = B.TAIKAINO 
		AND A.TAIKOSENNO = B.TAIKOSENNO 
	LEFT JOIN 
		(SELECT 
			C.KOJINSYOZOKUTIMEI 
		FROM 
			TBL_KYOGISYA AS C 
		WHERE 
			C.TAIKAINO = @TAIKAINO 
		GROUP BY 
		C.KOJINSYOZOKUTIMEI) AS KOJINSYOZOKU 
	ON 
		B.SYOZOKU = KOJINSYOZOKU.KOJINSYOZOKUTIMEI 
	LEFT JOIN 
		TBL_SYOZOKUTI AS D 
	ON 
		D.SYOZOKUTIMEI = B.SYOZOKU 
	WHERE 
		A.TAIKAINO = @TAIKAINO 
		AND A.TAIKOSENNO = @TAIKOSENNO 
	ORDER BY 
		B.JYUNI,
		B.TOKUTEN DESC,
		D.PRINTNO
